--EXEC SP_NEW_END_USER_END_CUST_REPORT_PROC
alter PROC SP_NEW_END_USER_END_CUST_REPORT_PROC
AS 
 BEGIN
	CREATE TABLE #FINAL_RESULTS 
 		(  [CUSTOMER NAME] VARCHAR(65), [NEW END USER] VARCHAR(65), [NEW END CUSTOMER] VARCHAR(65), [SALESORDER NO] VARCHAR(4000), [QUOTATION NO] VARCHAR(4000), [DIST SALESORDER NO] varchar(3000) )

 	CREATE TABLE #RESULTS 
 		(  [CUSTOMER NAME] VARCHAR(65), [NEW END USER] VARCHAR(65), [NEW END CUSTOMER] VARCHAR(65), [SALESORDER NO] VARCHAR(50), [QUOTATION NO] VARCHAR(50),  [DIST SALESORDER NO] varchar(50) )
 	INSERT INTO #RESULTS    
	SELECT DISTINCT C_NAME, NEW_END_USER, NEW_END_CUSTOMER, SALESORDER_NO, QUOTATION_NO, DIST_SALESORDER_NO   FROM 
	(SELECT CC.CUSTOMER_ID AS CUSTOMER_ID, C_NAME, SO_NEW_END_USER AS NEW_END_USER, SO_NEW_END_CUSTOMER AS NEW_END_CUSTOMER, SALESORDER_NO, NULL AS QUOTATION_NO, NULL AS DIST_SALESORDER_NO FROM SALES_ORDER SO, CUSTOMER CC WHERE CC.CUSTOMER_ID=SO.CUSTOMER_ID 
	UNION 
	SELECT C.CUSTOMER_ID AS CUSTOMER_ID, C_NAME, Q_NEW_END_USER AS NEW_END_USER, Q_NEW_END_CUSTOMER AS NEW_END_CUSTOMER, NULL AS SALESORDER_NO, QUOTATION_NO, NULL AS DIST_SALESORDER_NO FROM QUOTATION_TABLE QA, CUSTOMER C WHERE C.CUSTOMER_ID=QA.CUSTOMER_ID   
	union
	SELECT DSO.DISTRIBUTOR_ID AS CUSTOMER_ID, C_NAME, NULL AS NEW_END_USER, DSO.NEW_END_CUSTOMER_NAME AS NEW_END_CUSTOMER, NULL AS SALESORDER_NO, NULL AS QUOTATION_NO, DIST_SALESORDER_NO FROM DIST_SALES_ORDER DSO, CUSTOMER C WHERE C.CUSTOMER_ID=DSO.DISTRIBUTOR_ID  ) AS Q
	WHERE  
	(NEW_END_USER IS NOT NULL AND  NEW_END_USER != '') OR (NEW_END_CUSTOMER IS NOT NULL AND  NEW_END_CUSTOMER != '')
	ORDER BY C_NAME
--TODO
--	AND NEW_END_USER NOT IN (SELECT DISTINCT C_NAME AS END_USER_NAME FROM CUSTOMER_ENDCUSTOMER_RELATIONSHIP, CUSTOMER WHERE RELATIONSHIPSTATUS_ID = 1 AND PARENTCUSTOMER_ID = Q.CUSTOMER_ID AND CUSTOMER_ID = ENDCUSTOMER_ID)
--	AND NEW_END_CUSTOMER NOT IN (SELECT DISTINCT C_NAME FROM Customer_ODM_Relationship, CUSTOMER WHERE RELATIONSHIPSTATUS_ID = 1 AND PARENTCUSTOMER_ID = Q.CUSTOMER_ID AND CUSTOMER_ID = ODMCUSTOMER_ID)

	
 
	DECLARE @CUSTOMER_CURSOR CURSOR
	DECLARE @CUSTOMER_NAME VARCHAR(65)
	DECLARE @NEW_END_USER VARCHAR(65)
	DECLARE @NEW_END_CUSTOMER VARCHAR(65)
	DECLARE @SALESORDER_NO VARCHAR(50)
	DECLARE @QUOTATION_NO VARCHAR(50)
	DECLARE @DIST_SALESORDER_NO VARCHAR(50)
	SET @CUSTOMER_CURSOR = CURSOR FOR SELECT [CUSTOMER NAME], [NEW END USER], [NEW END CUSTOMER], [SALESORDER NO], [QUOTATION NO],  [DIST SALESORDER NO] FROM #RESULTS
	OPEN @CUSTOMER_CURSOR
	FETCH NEXT FROM @CUSTOMER_CURSOR INTO @CUSTOMER_NAME, @NEW_END_USER, @NEW_END_CUSTOMER, @SALESORDER_NO, @QUOTATION_NO, @DIST_SALESORDER_NO
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		IF ( (SELECT COUNT(*) FROM #FINAL_RESULTS WHERE [CUSTOMER NAME]=@CUSTOMER_NAME AND [NEW END USER]=@NEW_END_USER AND [NEW END CUSTOMER]=@NEW_END_CUSTOMER) = 0 )
		 BEGIN
			INSERT INTO #FINAL_RESULTS([CUSTOMER NAME], [NEW END USER], [NEW END CUSTOMER], [SALESORDER NO], [QUOTATION NO], [DIST SALESORDER NO] )
					VALUES ( @CUSTOMER_NAME, ISNULL(@NEW_END_USER,''), @NEW_END_CUSTOMER, ISNULL(@SALESORDER_NO,''), ISNULL(@QUOTATION_NO,''), ISNULL(@DIST_SALESORDER_NO,'') )
		 END
		ELSE--UPDATE
		 BEGIN
			IF ( @SALESORDER_NO IS NULL )
				SET @SALESORDER_NO = ''
			ELSE
				SET @SALESORDER_NO = ',' + @SALESORDER_NO 
			IF ( @QUOTATION_NO IS NULL )
				SET @QUOTATION_NO = ''
			ELSE
				SET @QUOTATION_NO = ',' + @QUOTATION_NO 
			IF ( @DIST_SALESORDER_NO IS NULL )
				SET @DIST_SALESORDER_NO = ''
			ELSE
				SET @DIST_SALESORDER_NO = ',' + @DIST_SALESORDER_NO 

			UPDATE #FINAL_RESULTS SET [SALESORDER NO] = [SALESORDER NO]+@SALESORDER_NO, [QUOTATION NO] = [QUOTATION NO] + @QUOTATION_NO, [DIST SALESORDER NO] = [DIST SALESORDER NO] + @DIST_SALESORDER_NO
				WHERE [CUSTOMER NAME]=@CUSTOMER_NAME AND [NEW END USER]=@NEW_END_USER AND [NEW END CUSTOMER]=@NEW_END_CUSTOMER
		 END
		SET @CUSTOMER_NAME = NULL
		SET @NEW_END_USER = NULL
		SET @NEW_END_CUSTOMER = NULL
		SET @SALESORDER_NO = NULL
		SET @QUOTATION_NO = NULL
		SET @DIST_SALESORDER_NO = NULL
		FETCH NEXT FROM @CUSTOMER_CURSOR INTO @CUSTOMER_NAME, @NEW_END_USER, @NEW_END_CUSTOMER, @SALESORDER_NO, @QUOTATION_NO, @DIST_SALESORDER_NO
	 END
	
	SELECT [CUSTOMER NAME],[NEW END USER], [NEW END CUSTOMER], [SALESORDER NO], [QUOTATION NO], [DIST SALESORDER NO]
	FROM #FINAL_RESULTS ORDER BY [CUSTOMER NAME]
END


